import requests as rq
from bs4 import BeautifulSoup
import pymysql as mysql

name_list = []
score_list = []
star_list = []
region_list = []
region_rank_list = []
type_list = []

sql_conn = mysql.connect(host="127.0.0.1", user="root", password="hzkjzyjsxy", database="schools")
cursor = sql_conn.cursor()
for page in range(1, 11):
    body = rq.get("http://www.nseac.com/eva/GZE.php?DDLyear=2023&page={}".format(page))
    body.encoding = body.apparent_encoding
    bs = BeautifulSoup(body.text, features="html.parser")
    table = bs.select("table")[0]
    trs = table.select("tr")
    for index, tr in enumerate(trs):
        if index == 0:
            continue
        for i, td in enumerate(tr.select("td")):
            if i == 1:
                name_list.append(td.text)
            elif i == 2:
                score_list.append(td.text)
            elif i == 3:
                star_list.append(td.text)
            elif i == 4:
                region_list.append(td.text)
            elif i == 5:
                region_rank_list.append(td.text)
            elif i == 6:
                type_list.append(td.text)

for name, score, star, region, region_rank, school_type in zip(name_list, score_list, star_list, region_list,
                                                               region_rank_list, type_list):
    sql = "insert into school(name,score,region,region_rank,type,star) values(\"{}\",\"{}\",\"{}\",\"{}\",\"{}\",\"{}\")".format(
        name, score,
        region, region_rank,
        school_type, star)
    insertLength = cursor.execute(sql)
    print(insertLength)
sql_conn.commit()  # 提交
sql_conn.close()
